﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;
using KP_CommonLibrary.KP_ObjectReferences;
using System.Globalization;

namespace KP_CommonLibrary.KP_DataAccess
{
    public class KP_DALListBase : KP_DALBase
    {
        public const string PrefixStore = "kp_";
        public const string Pattern = "[dbo].[{0}{1}{2}{3}]";
        public const string SuffixStore = "";
        private string ms_TableName;
        public string TableName
        {
            get
            {
                return ms_TableName;
            }
            set
            {
                ms_TableName = value;
                if (String.IsNullOrEmpty(InsertStore))
                    InsertStore = string.Format(Pattern, PrefixStore, GetEntityName(ms_TableName), "Insert", SuffixStore);

                if (String.IsNullOrEmpty(UpdateStore))
                    UpdateStore = string.Format(Pattern, PrefixStore, GetEntityName(ms_TableName), "Update", SuffixStore);

                if (String.IsNullOrEmpty(DeleteStore))
                    DeleteStore = string.Format(Pattern, PrefixStore, GetEntityName(ms_TableName), "Delete", SuffixStore);

                if (String.IsNullOrEmpty(SelectStore))
                    SelectStore = string.Format(Pattern, PrefixStore, GetEntityName(ms_TableName), "Select", "");

                if (String.IsNullOrEmpty(ChangeIdentity))
                    ChangeIdentity = string.Format(Pattern, PrefixStore, GetEntityName(ms_TableName), "ChangeIdentity", SuffixStore);

                if (String.IsNullOrEmpty(CanDeleteStore))
                    CanDeleteStore = string.Format(Pattern, PrefixStore, GetEntityName(ms_TableName), "CanDelete", SuffixStore);

            }

        }

        public string InsertStore { get; set; }
        public string UpdateStore { get; set; }
        public string DeleteStore { get; set; }
        public string SelectStore { get; set; }
        public string ChangeIdentity { get; set; }
        public string CanDeleteStore { get; set; }

        #region using Builder
        // SqlCommandBuilder commandBuilder;
        //SqlDataAdapter adapter;
        #endregion

        public KP_DALListBase()
        {
        }
        //public DALListBase(string tabbleName, bool usingStore)
        //{
        //    if (usingStore)
        //        TableName = tabbleName;
        //    else
        //    {
        //        ms_TableName = tabbleName;
        //        adapter
        //    }
        //}
        public KP_DALListBase(string tableName)
        {
            TableName = tableName;
        }

        public KP_DALListBase(string tableName, string insertName, string updateName, string deleteName, string selectName, string ChangeName, string canDeleteName)
        {
            InsertStore = insertName;
            UpdateStore = updateName;
            DeleteStore = deleteName;
            SelectStore = selectName;
            ChangeIdentity = ChangeName;
            CanDeleteStore = canDeleteName;
            TableName = tableName;
        }

        /// <summary>
        /// Kiêm tra dữ liệu trước khi thực hiện các thao tác Insert, Update, Delete
        /// Nếu có lỗi thì throw Exception để BL hoặc UI bắt và thông báo lỗi.
        /// </summary>
        /// <param name="item"></param>
        /// <param name="mode"></param>
        public virtual void Validate(DataRow item, ValidateMode mode)
        {
        }



        /// <summary>
        /// Thêm bản ghi mới vào bảng
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public virtual bool Insert(SqlTransaction transaction, DataRow item)
        {
            Validate(item, ValidateMode.Insert);
            if (transaction != null)
                return SqlHelper.ExecuteNonQuery(transaction, InsertStore, item) > 0;
            else
                return SqlHelper.ExecuteNonQuery(KP_OCnnSql.Cnn_String, InsertStore, item) > 0;
        }
        public virtual bool Update(SqlTransaction transaction, DataRow item)
        {
            Validate(item, ValidateMode.Update);
            if (transaction == null)
                return SqlHelper.ExecuteNonQuery(KP_OCnnSql.Cnn_String, UpdateStore, item) > 0;
            else
                return SqlHelper.ExecuteNonQuery(transaction, UpdateStore, item) > 0;
        }
        public virtual bool Delete(SqlTransaction transaction, DataRow item)
        {
            Validate(item, ValidateMode.Delete);
            if (transaction == null)
                return SqlHelper.ExecuteNonQuery(KP_OCnnSql.Cnn_String, DeleteStore, item) > 0;
            else
                return SqlHelper.ExecuteNonQuery(transaction, DeleteStore, item) > 0;

        }

        public virtual void Fill(DataSet ds, string whereCondition, bool clearBeforeFill, params string[] fields)
        {
            SqlConnection connection = new SqlConnection(KP_OCnnSql.Cnn_String);
            SqlDataAdapter adapter = null;

            if (clearBeforeFill && ds.Tables.Contains(TableName))
                ds.Tables[TableName].Clear();

            string fieldSelects = " * ";
            if (fields != null)
                if (fields.Length > 1)
                    fieldSelects = String.Join(", ", fields);
                else
                    fieldSelects = fields.Length == 1 ? fields[0] : " * ";
            string sql = "Select " + fieldSelects + " from " + TableName;
            if (!string.IsNullOrEmpty(whereCondition))
                sql += " Where " + whereCondition;

            try
            {
                connection.Open();
                adapter = new SqlDataAdapter(sql, connection);
                adapter.Fill(ds, TableName);

            }
            catch (Exception ex)
            {
                throw new Exception("DAL Fill:" + ex.Message, ex);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();

                if (connection.State != ConnectionState.Closed)
                    connection.Close();
            }

        }
        public virtual void Fill(DataTable tbl, string whereCondition, bool clearBeforeFill, params string[] fields)
        {
            Fill(tbl, whereCondition, "", clearBeforeFill, fields);
        }
        public virtual void Fill(DataTable tbl, string whereCondition, string order, bool clearBeforeFill, params string[] fields)
        {
            SqlConnection connection = new SqlConnection(KP_OCnnSql.Cnn_String);
            SqlDataAdapter adapter = null;

            if (clearBeforeFill)
                tbl.Clear();

            string fieldSelects = " * ";
            if (fields != null)
                if (fields.Length > 1)
                    fieldSelects = String.Join(", ", fields);
                else
                    fieldSelects = fields.Length == 1 ? fields[0] : " * ";
            string sql = "Select " + fieldSelects + " from " + TableName;

            if (!string.IsNullOrEmpty(whereCondition))
                sql += " Where " + whereCondition;
            if (!string.IsNullOrEmpty(order))
                sql += " Order By " + order;
            try
            {
                connection.Open();
                adapter = new SqlDataAdapter(sql, connection);
                adapter.Fill(tbl);

            }
            catch (Exception ex)
            {
                throw new Exception("DAL Fill:" + ex.Message, ex);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();

                if (connection.State != ConnectionState.Closed)
                    connection.Close();
            }

        }

        public virtual DataTable Select(string whereCondition, string orderBy, params string[] fields)
        {
            string fieldSelects = " * ";
            if (fields != null)
                if (fields.Length > 1)
                    fieldSelects = String.Join(", ", fields);
                else
                    fieldSelects = fields.Length == 1 ? fields[0] : " * ";

            return SqlHelper.ExecuteDataTable(KP_OCnnSql.Cnn_String, SelectDynamicProc, fieldSelects, TableName, whereCondition, orderBy);
        }

        /// <summary>
        /// Chưa thực hiện
        /// </summary>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public virtual DataTable Select(object[] parameterValues)
        {
            throw new NotImplementedException();
        }
        public virtual DataTable GetSchema()
        {
            return KP_DALBase.GetSchema(TableName);
            //return GetSchemaTable(TableName);
        }
        public static DataTable GetSchemaTable(string tableName)
        {
            SqlConnection conn = new SqlConnection(KP_OCnnSql.Cnn_String);
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("Select Top 1 * from " + tableName, conn);
                SqlDataReader enumQueryResults = command.ExecuteReader();
                DataTable table = new DataTable(tableName);
                table.Locale = CultureInfo.InvariantCulture;
                for (int i = 0; i < enumQueryResults.FieldCount; i++)
                {
                    table.Columns.Add(enumQueryResults.GetName(i), enumQueryResults.GetFieldType(i));
                }
                return table;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            //return SqlHelper.ExecuteDataTable(KP_OCnnSql.Cnn_String, CommandType.Text, "Select Top 0 from " + tableName);

        }


        public virtual DataTable Select()
        {
            return SqlHelper.ExecuteDataTable(KP_OCnnSql.Cnn_String, CommandType.Text, "Select * from " + TableName);
        }
    }



    //public class ConstString
    //{
    //    public const string Msg1 = "Can not delete item {0} by {1}";
    //    public const string Msg2 = "Can not insert this item by {0}";
    //    public const string Msg3 = "Can not update this item by {0}";

    //}


}
